<?php
/**
 * DBDriver for MySQL.
 *
 * @author Martin "DevelX" Jurča
 * @version 2009-10-20
 */
class DBDriverMySQL {
    /**
     * MySQL database connection via MySQL extension.
     *
     * @var MySQL Connection to MySQL database
     */
    private $db;
    private $tableLinks;
    private $insertId;

    public function __construct($dbaccess) {
        $check = '/^[^:]+(:[^@]*)?@[^:]+(:[^\/]*)?\/\/[^?]+([?].*)?$/';
        if (preg_match($check, $dbaccess) == 0) {
            throw new Exception("invalid database access string");
        }

        list($userpassword, $serverport) = $this->splitString($dbaccess, '@');
        list($user, $password) = $this->splitString($userpassword, ':');
        list($serverport, $dbname) = $this->splitString($serverport, '//');
        list($server, $port) = $this->splitString($serverport, ':');

        $this->db = mysql_connect($server, $user, $password);
        if (!is_resource($this->db)) {
            throw new Exception("Connection to database failed: " .
                    mysql_error());
        }
        mysql_select_db($dbname, $this->db);
        mysql_set_charset('utf8', $this->db);
    }

    public function createTable($tableName, &$columns) {
        $sql = "CREATE TABLE `$tableName` (";
        $cols = "";
        foreach ($columns as $name => $type) {
            $cols .= ($cols == '' ? '' : ',') .
                    $this->getColumnType($name, $type);
        }
        $sql .= "$cols) TYPE=innodb DEFAULT CHARSET=utf8 COLLATE=utf8_bin";
        return mysql_query($sql, $this->db);
    }

    public function alterTable($tableName, $changes) {
        $sql = "ALTER TABLE `$tableName` ";
        $cols = "";
        foreach ($changes as $name => $type) {
            $cols .= ($cols == '' ? '' : ',') .
                    $this->getAlterColumnType($name, $type);
        }
        $sql .= $cols;
        Cache::flushTable($tableName);
        return mysql_query($sql, $this->db);
    }

    public function dropTable($tableName) {
        $sql = "DROP TABLE `$tableName`";
        Cache::flushTable($tableName);
        return mysql_query($sql, $this->db);
    }

    public function insert($tableName, &$values) {
        $sql = "INSERT INTO `$tableName` (";
        $cols = "";
        $vals = "";
        foreach ($values as $col => $val) {
            $cols .= ($cols == '' ? '' : ',') . "`$col`";
            if (is_string($val)) {
                $val = $this->escapeString($val);
                $vals .= ($vals == '' ? '' : ',') . "'$val'";
            } else {
                if ($val === null) {
                    $val = 'null';
                }
                $vals .= ($vals == '' ? '' : ',') . "$val";
            }
        }
        $sql .= "$cols) VALUES ($vals)";
        Cache::flushTable($tableName);
        mysql_query($sql, $this->db);
        $this->insertId = mysql_insert_id($this->db);
        return true;
    }

    public function &select($tableName, &$columns = array(),
            $conditions = '', &$orderBy = array(), $limitFrom = 0,
            $limitCount = -1, $multiple = false) {
        $sql = "SELECT ";
        if (count($columns) > 0) {
            $cols = "";
            foreach ($columns as $col) {
                $cols .= ($cols == '' ? '' : ',') .
                        (strpos($col, ' ') === false ? (strpos($col, '(') ===
                                false ? "`$col`" : $col) : $col);
            }
            $sql .= $cols;
        } else {
            $sql .= "*";
        }
        $sql .= " FROM `$tableName` " . ($multiple ?
                $this->getMultipleSelect($tableName) : '') .
                ($conditions != '' ? " WHERE $conditions " : '');
        if (count($orderBy) > 0) {
            $order = "";
            foreach ($orderBy as $entity) {
                $order .= ($order == '' ? '' : ',') .
                        (substr($entity, 0, 1) == '!' ?
                        (strpos($entity, '(') !== false ? substr($entity, 1) .
                        " DESC" : "`" . substr($entity, 1) . "` DESC") :
                        (strpos($entity, '(') !== false ? $entity :
                        "`$entity`"));
            }
            $sql .= "ORDER BY $order ";
        }
        if (($limitFrom > 0) || ($limitCount >= 0)) {
            if ($limitCount == -1) {
                $limitCount = 65535;
            }
            $sql .= "LIMIT $limitFrom,$limitCount ";
        }
        $data = Cache::dbQuery($tableName, $sql);
        if ($data === false) {
            $data = &$this->fetchRows($sql);
            Cache::dbSaveQuery($tableName, $sql, $data);
        }
        return $data;
    }

    public function update($tableName, &$values, $conditions = '') {
        $sql = "UPDATE `$tableName` SET ";
        $assigns = "";
        foreach ($values as $column => $value) {
            $assigns .= ($assigns == '' ? '' : ',') . "`$column` = " .
                    (is_string($value) ?
                    "'{$this->escapeString($value)}'" : (is_null($value) ?
                    'null' : $value));
        }
        $sql .= "$assigns" . ($conditions == '' ? '' : " WHERE $conditions");
        Cache::flushTable($tableName);
        mysql_query($sql, $this->db);
        return true;
    }

    public function delete($tableName, $conditions = '') {
        $sql = "DELETE FROM `$tableName`" . ($conditions == '' ? '' :
                " WHERE $conditions");
        Cache::flushTable($tableName);
        return mysql_query($sql, $this->db);
    }

    public function getError() {
        return mysql_error($this->db);
    }

    public function close() {
        return mysql_close($this->db);
    }

    public function startTransaction() {
        return mysql_query("START TRANSACTION", $this->db);
    }

    public function commitTransation() {
        return mysql_query("COMMIT", $this->db);
    }

    public function rollbackTransaction() {
        return mysql_query("ROLLBACK", $this->db);
    }

    public function getLastInsertId() {
        return $this->insertId;
    }

    public function setTableLink($target, $refferer) {
        list($srcTable, $srcColumn) = $this->splitString($refferer, '/');
        list($trgTable, $trgColumn) = $this->splitString($target, '/');

        $record = array('srcTable' => $srcTable, 'srcColumn' => $srcColumn,
                'trgColumn' => $trgColumn);
        $this->tableLinks[$trgTable][] = $record;
        return true;
    }

    public function escapeString($original) {
        return mysql_real_escape_string($original, $this->db);
    }

    /**
     * Executes given SQL command and fetch result rows into both associative
     * and numeric array.
     *
     * @param String $sql SQL command to execute.
     * @return Array Array of selected rows
     */
    private function &fetchRows($sql) {
        $result = mysql_query($sql, $this->db);
        if (mysql_errno($this->db)) {
            $result = false;
            return $result;
        }

        $data = array();
        $row = mysql_fetch_array($result);
        while ($row != null) {
            $data[] = $row;
            $row = mysql_fetch_array($result);
        }
        return $data;
    }

    /**
     * Returns SQL join for connected table.
     *
     * @param String $tableName Name of the table to which other table
     *        references.
     * @return String SQL table join
     */
    private function getMultipleSelect($tableName) {
        $refferers = $this->tableLinks[$tableName];
        $sql = "";
        foreach ($refferers as $refferer) {
            $sql .= "INNER JOIN `{$refferer['srcTable']}` ON " .
                    "`{$refferer['srcTable']}`.`{$refferer['srcColumn']}` = " .
                    "`$tableName`.`{$refferer['trgColumn']}` ";
        }
        return $sql;
    }

    /**
     * Parses platform-independent representation of column changes into SQL.
     *
     * @param String $name Name of the column
     * @param String $type Platform-independent representation of column
     *        changes.
     * @return String SQL string representation of column changes.
     */
    private function getAlterColumnType($name, $type) {
        if (strpos($type, 'A') !== false) {
            $sql = "ADD `$name` ";
        } elseif (strpos($type, 'D') !== false) {
            $sql = "DROP `$name` ";
        } else {
            $sql = "CHANGE `$name` `$name` ";
        }

        $shortType = $type;
        while (strpos($shortType, '\'') !== false) {
            $shortType = preg_replace('/^(.*?)\'.*?\'(.*)$/', '\\1\\2',
                    $shortType);
        }

        if (strpos($shortType, 'T') !== false) {
            $sql .= $this->getColumnDataType($type, $shortType);
        }

        $sql .= $this->getColumnIndexDetails($shortType, $name);

        $sql .= $this->getColumnOtherDetails($shortType, $type, $name);

        return $sql;
    }

    /**
     * Parses platform-independent representation of column type into SQL form.
     *
     * @param String $name Name of the column
     * @param String $type Platform-independent representation of column type.
     * @return Striung SQL string representation of column type.
     */
    private function getColumnType($name, $type) {
        $shortType = $type;
        while (strpos($shortType, '\'') !== false) {
            $shortType = preg_replace('/^(.*?)\'[^\']*\'(.*)$/', '\\1\\2',
                    $shortType);
        }
        $sql = "`$name` ";

        $sql .= $this->getColumnDataType($type, $shortType);

        $sql .= $this->getColumnOtherDetails($type, $type, $name);

        $sql .= $this->getColumnIndexDetails($shortType, $name);

        return $sql;
    }

    /**
     * SQL string representation of other column details according to given
     * platform-independent representation.
     *
     * @param String $shortType Column details without inner strings.
     * @param String $type Column details.
     * @param String $name Column name.
     * @return String SQL representation of other column details.
     */
    private function getColumnOtherDetails($shortType, $type, $name) {
        $return = '';
        if (strpos($shortType, '+') !== false) {
            $return .= "AUTO_INCREMENT ";
        }
        if (strpos($shortType, 'N') !== false) {
            $return .= "NOT NULL ";
        } elseif (strpos($shortType, 'n') !== false) {
            $return .= "NULL ";
        }
        if (strpos($shortType, 'C') !== false) {
            $comment = $this->getInnerString($type, 'C');
            $comment = mysql_real_escape_string($comment, $this->db);
            $return .= "COMMENT '$comment' ";
        }
        if (strpos($shortType, 'a') !== false) {
            $default = $this->getInnerString($type, 'a');
            $default = mysql_real_escape_string($default, $this->db);
            $return .= "DEFAULT '$default' ";
        }
        if (strpos($shortType, 'F') !== false) {
            $tablecolumn = preg_replace('/^.*?F(.+?)\((.+?)\).*$/', '\\1(\\2)',
                    $shortType);
            $return .= ", FOREIGN KEY ($name) REFERENCES $tablecolumn";
        }
        return $return;
    }

    /**
     * Returns SQL string representation of indexing details according to
     * given platform-independent representation.
     *
     * @param String $shortType Type of the column without inner strings.
     * @param String $name Name of the column.
     * @return String SQL string representation of indexing details.
     */
    private function getColumnIndexDetails($shortType, $name) {
        $return = '';
        if (strpos($shortType, 'P') !== false) {
            $return .= ", PRIMARY KEY (`$name`) ";
        }
        if (strpos($shortType, 'U') !== false) {
            $return .= ", UNIQUE (`$name`) ";
        }
        if (strpos($shortType, 'I') !== false) {
            $return .= ", INDEX(`$name`) ";
        }
        return $return;
    }

    /**
     * Returns SQL data type according to platform-independet representation.
     *
     * @param String $type Complete column type definition
     * @param String $shortType Column type definition without inner strings.
     * @return String SQL data type for the column
     */
    private function getColumnDataType($type, $shortType) {
        if (strpos($shortType, 'i') !== false) {
            return "INT ";
        } elseif (strpos($shortType, 'f') !== false) {
            return "FLOAT ";
        } elseif (strpos($shortType, 'd') !== false) {
            return "DATE ";
        } elseif (strpos($shortType, 't') !== false) {
            return "TIMESTAMP ";
        } elseif (strpos($shortType, 'v') !== false) {
            $length = (int) $this->getInnerString($type, 'v');
            return "VARCHAR($length) ";
        } elseif (strpos($shortType, 'c') !== false) {
            $length = (int) $this->getInnerString($type, 'c');
            return "CHAR($length) ";
        } elseif (strpos($shortType, 's') !== false) {
            return "TINYTEXT ";
        } elseif (strpos($shortType, 'T') !== false) {
            return "TEXT ";
        } elseif (strpos($shortType, 'l') !== false) {
            return "MEDIUMTEXT ";
        } elseif (strpos($shortType, 'L') !== false) {
            return "LONGTEXT ";
        }
        return '';
    }

    /**
     * Locates in string inner string surrounded by apostrophes with hiven
     * head as prefix preceeding the string surrounded by apostrophes.
     *
     * @param String $source Source string with inner strings.
     * @param String $head
     * @return String Requested inner string.
     */
    private function getInnerString($source, $head) {
        while ($source != '') {
            $pos = strpos($source, '\'');
            if (($pos !== false) && ($pos > 0)) {
                $endpos = strpos($source, '\'', $pos + 1);
                if (substr($source, $pos - 1, 1) == $head) {
                    return substr($source, $pos + 1, $endpos - $pos - 1);
                } else {
                    if ($endpos === false) {
                        return '';
                    } else {
                        $source = substr($source, $endpos + 1);
                    }
                }
            } else {
                return '';
            }
        }
        return '';
    }

    /**
     * Splits string to two by the splitter string. If the string is not
     * located in original string, the method returns first string equalt to
     * original and second empty string.
     *
     * @param String $origin String to split.
     * @param String $splitter String that should be used to split the original
     *        string.
     * @return Arrray Array of two string.
     */
    private function splitString($origin, $splitter) {
        if (strpos($origin, $splitter) === false) {
            return array($origin, '');
        } else {
            $part1 = substr($origin, 0, strpos($origin, $splitter));
            $part2 = substr($origin, strpos($origin, $splitter) +
                    strlen($splitter));
            return array($part1, $part2);
        }
    }

    public function  __destruct() {
        $this->close();
    }
}
?>
